﻿CREATE VIEW dbo.vwParliamentarianSearch
AS
SELECT DISTINCT 
                      P.CPALId, P.FirstName, P.PreferredName, P.OtherNames, P.Surname, P.Title, P.Honorific, P.Electorate, P.State, P.Chamber, P.MPID, P.FormerMember, P.Gender, 
                      P.EmailAddress, P.FacebookAddress, P.TwitterAddress, P.PersonalWebsite, dbo.Parties.FullName AS PartyFullName, 
                      dbo.Parties.AbbreviatedName AS PartyAbbreviatedName, dbo.fnFirstTitle(P.CPALId) AS FirstTitle, dbo.fnAllTitles(P.CPALId) AS AllTitles, P.EmailVisible, 
                      P.ContactFormVisible
FROM         dbo.Parties RIGHT OUTER JOIN
                      dbo.Parliamentarians AS P ON dbo.Parties.RecId = P.PartyRecId
WHERE     (P.CPALId IS NOT NULL)